﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using AspNetCs.Config;

/*----------------------------------------------------------------
            // Copyright (C) 2008  万万网(OneOne.Cn)   版权所有。
            // 文件名：DataProvider,数据库链接与处理
            // 文件功能描述：
            //   用于与数据库通信,将数据库返回或者处理
            // 
            // 创建标识：Wangwei 2008-9-11
            //
            // 修改标识：
            // 修改描述： 
         调用方法:
        dbclass 实例名=new dbclass()  //实例化数据库
        dbclass.ProceducreParameter[] Par = new dbclass.ProceducreParameter[1];//1表示参数的个数
        Par[0].dbType = SqlDbType.NVarChar; //数据字段类型
        Par[0].ObjValue = 传入的参数值;
        Par[0].strName = "@username";//存储过程参数名
        实例名.方法(参数一,参数二,.....,参数N);
        实例名.Close()//关闭数据库链接
//----------------------------------------------------------------*/
/// 数据库链接
/// <summary>
/// DataProvider 的摘要说明
/// </summary>
public class dbclass
{
    private SqlConnection _Connection;//定义_Connection为SqlConnection对象,

    /// <summary>
    /// 链接数据库
    /// </summary>
    public dbclass()
    {
        this._Connection = this.GetConnStr("SqlConnectionString");
    }
    /// <summary>
    /// 根据自己建立的Key,链接数据库
    /// </summary>
    /// <param name="key">用于数据库链接的Key</param>
    public dbclass(string key)
    {
        this._Connection = this.GetConnStr(key);
    }

    /// <summary>
    /// 根据key,建立数据库链接
    /// </summary>
    /// <param name="key">web.Config中用于数据库链接的Key</param>
    /// <returns></returns>
    public SqlConnection GetConnStr(string key)
    {
        //if (ConfigurationManager.AppSettings[key] == null)
        //{
        //    throw new Exception("数据库连接参数配制出错");
        //}
        //string ConnectionStr = ConfigurationManager.AppSettings[key];
        string ConnectionStr = BaseConfig.GetBaseConfig().DbConnectstring;
        this._Connection = new SqlConnection(ConnectionStr);
        return this._Connection;
    }

    /// <summary>
    /// 定义一个结构体(设置存储过程中的参数的基本属性)
    /// </summary>
    public struct ProceducreParameter
    {
        public string strName;  //存储过程参数名称
        public SqlDbType dbType; //参数类型
        public int Size;        //参数大小
        public object ObjValue; //参数传入的变量值
        public ParameterDirection PdDirection;//表示此参数是否为输出
    }
    /// <summary>
    /// 返回SQlCommnad对象
    /// </summary>
    /// <param name="storedProcNameOrSqlString">存储过程名称或者SQL</param>
    /// <param name="Parameter">参数数组</param>
    /// <param name="SType">Command类型</param>
    /// <returns></returns>
    private SqlCommand BuildQueryCommand(string storedProcNameOrSqlString, ProceducreParameter[] Parameter, CommandType SType)
    {
        SqlCommand Command = new SqlCommand(storedProcNameOrSqlString, this._Connection);
        Command.CommandType = SType;
        for (int i = 0; i < Parameter.Length; i++)
        {
            ProceducreParameter StrPro = Parameter[i];
            SqlParameter sp = new SqlParameter(StrPro.strName, StrPro.dbType, StrPro.Size);
            sp.Direction = StrPro.PdDirection;
            sp.Value = StrPro.ObjValue;
            Command.Parameters.Add(sp);
        }
        return Command;

    }



    /// <summary>
    /// 返回存储过程的SQLCommand对象,该方法只能操作存储过程
    /// </summary>
    /// <param name="storedProcNameOrSqlString">存储过程名称</param>
    /// <param name="Parameter">参数组</param>
    /// <returns></returns>
    private SqlCommand BuildQueryCommand(string storedProcNameOrSqlString, ProceducreParameter[] Parameter)
    {
        return this.BuildQueryCommand(storedProcNameOrSqlString, Parameter, CommandType.StoredProcedure);
    }

    /// <summary>
    /// 返回SQL语句的SQLCommand对象,该方法只能执行SQl语句
    /// </summary>
    /// <param name="storedProcNameOrSqlString">SQL语句</param>
    /// <param name="Parameter"></param>
    /// <returns></returns>
    private SqlCommand BuildTextCommand(string storedProcNameOrSqlString, ProceducreParameter[] Parameter)
    {
        return this.BuildQueryCommand(storedProcNameOrSqlString, Parameter, CommandType.Text);

    }

    /// <summary>
    /// 返回存储过程的ReturnVale值
    /// </summary>
    /// <param name="storedProcNameOrSqlString">存储过程名称</param>
    /// <param name="Parameter">参数组</param>
    /// <returns></returns>
    private SqlCommand BuildIntCommand(string storedProcNameOrSqlString, ProceducreParameter[] Parameter)
    {
        SqlCommand Command = this.BuildQueryCommand(storedProcNameOrSqlString, Parameter);
        Command.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, true, 0, 0, string.Empty, DataRowVersion.Default, null));
        return Command;
    }




    #region 数据处理[返回]


    /// <summary>
    /// 执行存储过程返回DATASET，并同时返回存储过程的OutPut,只能返回一个OutPut
    /// </summary>
    /// <param name="storedProcNameOrSqlString">存储过程名称</param>
    /// <param name="Parameter">存储过程参数组</param>
    /// <param name="Re">需要返回的存储过程名称，参数的@不能少</param>
    /// <param name="Values">OUTPUT返回的值,类型为OBJECT</param>
    /// <returns></returns>
    public DataSet GetProcedu_Output(string storedProcNameOrSqlString, ProceducreParameter[] Parameter,string Re,out object Values)
    {
        DataSet ds = new DataSet();
        SqlDataAdapter adp = new SqlDataAdapter();
        this._Connection.Open();
        SqlCommand command = this.BuildQueryCommand(storedProcNameOrSqlString, Parameter, CommandType.StoredProcedure);
        adp.SelectCommand = command;
        adp.Fill(ds);
        Values = command.Parameters[Re].Value;
        return ds;
    }


    /// <summary>
    /// 执行SQL，返回DataReader
    /// </summary>
    /// <param name="StrSql">SQL的语句</param>
    /// <returns></returns>
    public SqlDataReader GetSqlDataReader(string StrSql)
    {
        //try
        //{
            dbclass.ProceducreParameter[] Par = new ProceducreParameter[0];
            this._Connection.Open();
            SqlCommand Command = this.BuildTextCommand(StrSql, Par);
            return Command.ExecuteReader(CommandBehavior.CloseConnection);
        //}
        //catch (SqlException Ex)
        //{
        //    throw Ex;
        //}
    }







    /// <summary>
    /// 执行SQL,无返回
    /// </summary>
    /// <param name="SqlString">SQL语句</param>
    public void Execute_Noget(string SqlString)
    {
        try
        {
            this._Connection.Open();
            dbclass.ProceducreParameter[] par = new ProceducreParameter[0];
            SqlCommand Command = this.BuildTextCommand(SqlString, par);
            Command.ExecuteNonQuery();
        }
        finally
        {
            this._Connection.Close();
            close();
        }

    }




    /// <summary>
    /// 执行SQL返回首行首列
    /// </summary>
    /// <param name="Strsql"></param>
    /// <returns></returns>
    public object GetOneFile(string Strsql)
    {
        object obj = null;
        SqlDataAdapter adapter = new SqlDataAdapter(Strsql, this._Connection);
        DataSet ds = new DataSet();
        try
        {
            this._Connection.Open();
            adapter.Fill(ds);
            if (ds.Tables[0].Rows.Count > 0)
            {
                obj = ds.Tables[0].Rows[0][0];
            }
        }
        finally
        {
            this._Connection.Close();

        }
        return obj;


    }
    /// <summary>
    /// 执行SQL返回DATASET
    /// </summary>
    /// <param name="Sqlstr"></param>
    /// <returns></returns>
    public DataSet GetSql_DataSet(string Sqlstr)
    {
        try
        {
            DataSet ds = new DataSet();
            this._Connection.Open();
            dbclass.ProceducreParameter[] par = new ProceducreParameter[0];
            SqlDataAdapter adp = new SqlDataAdapter();
            adp.SelectCommand = this.BuildTextCommand(Sqlstr, par);
            adp.Fill(ds);
            return ds;

        }
        catch (SqlException q)
        {
            throw q;
        }
        finally
        {
            this._Connection.Close();
            close();
        }
    }
    /// <summary>
    /// 执行SQL，返回DataTable
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public DataTable GetSql_DataTable(string sql)
    {  
        this._Connection.Open();
        DataTable dt = new DataTable();
        try
        {
            dbclass.ProceducreParameter[] par = new ProceducreParameter[0];
            SqlDataAdapter adp = new SqlDataAdapter();
            adp.SelectCommand = this.BuildTextCommand(sql, par);
            adp.Fill(dt);
        }
        finally {
            this._Connection.Close(); 
        }
        return dt;
    }


    /// <summary>
    ///返回DATASET
    /// </summary>
    /// <param name="sql">SQL查询语句</param>
    /// <param name="currentPage">当前页</param>
    /// <param name="pagesize">分页数</param>
    /// <param name="recordcount">out 返回总数[int]</param>
    /// <returns></returns>
    public DataSet GetPage(string sql, int currentPage, int pagesize, out int recordcount)
    {
        recordcount = GetPageRecord(sql);
        if (currentPage > recordcount)
        {

            currentPage = recordcount;

        }
        if (currentPage <= 0)
        {

            currentPage = 1;
        }

        DataSet ds = new DataSet();
        int startRow = (currentPage - 1) * pagesize;



        SqlDataAdapter sad = new SqlDataAdapter(sql, this._Connection);
        sad.Fill(ds, startRow, pagesize, "PagerTable");
        return ds;
    }


    public int GetPageRecord(string sql)
    {
        sql = System.Text.RegularExpressions.Regex.Replace(sql.ToLower(), "order by.*", "");
        sql = "select count(*) from (" + sql + ") as temp";
        SqlCommand cmd = new SqlCommand(sql, this._Connection);
        cmd.Connection.Open();
        int recordcount = (int)cmd.ExecuteScalar();
        return recordcount;
    }












    /// <summary>
    /// 执行存储过程，返回DATASET
    /// </summary>
    /// <param name="storedProcName">存储过程名称</param>
    /// <param name="Param">参数组</param>
    /// <returns></returns>
    public DataSet GetProcedure_DataSet(String storedProcName, ProceducreParameter[] Param)
    {
        try
        {
            DataSet ds = new DataSet();
            this._Connection.Open();
            SqlDataAdapter adp = new SqlDataAdapter();
            adp.SelectCommand = this.BuildQueryCommand(storedProcName, Param);
            adp.Fill(ds);
            return ds;
        }
        catch (SqlException q)
        {
            throw q;
        }
        finally
        {
            this._Connection.Close();
            close();
        }
    }


    /// <summary>
    /// 执行存储过程，返回DATASET
    /// </summary>
    /// <param name="storedProcName">存储过程名称</param>
    /// <param name="Param">参数组</param>
    /// <param name="Tablename">DATASET表名</param>
    /// <returns></returns>
    public DataSet GetProcedure_DataSet(String storedProcName, ProceducreParameter[] Param,string Tablename)
    {
        try
        {
            DataSet ds = new DataSet();
            this._Connection.Open();
            SqlDataAdapter adp = new SqlDataAdapter();
            adp.SelectCommand = this.BuildQueryCommand(storedProcName, Param);
            adp.Fill(ds, Tablename);
            return ds;
        }
        catch (SqlException q)
        {
            throw q;
        }
        finally
        {
            this._Connection.Close();
            close();
        }
    }








    /// <summary>
    /// 执行存储过程，返回DataReader
    /// </summary>
    /// <param name="storedProcName">存储过程名称</param>
    /// <param name="Param"></param>
    /// <returns></returns>
    public SqlDataReader GetProcedure_DataReader(string storedProcName, ProceducreParameter[] Param)
    {
        SqlDataReader dr;
       
        try
        {
            this._Connection.Open();
            SqlCommand Command = this.BuildQueryCommand(storedProcName, Param);
            dr = Command.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch (SqlException e)
        {
            this._Connection.Close();
            throw e;

        }
        return dr;
    }

    /// <summary>
    /// 执行无参数存储过程，返回DataReader
    /// </summary>
    /// <param name="storedProcName">存储过程名称</param>
    /// <returns></returns>
    public SqlDataReader GetProcedureNoParam_DataReader(string storedProcName)
    {
        try
        {

            SqlCommand Command = new SqlCommand(storedProcName, this._Connection);
            Command.CommandType = CommandType.StoredProcedure;
            this._Connection.Open();
            return Command.ExecuteReader(CommandBehavior.CloseConnection);
        }
        finally
        {
            this._Connection.Close();

        }
        
    }
    /// <summary>
    /// 执行带参数存储过程，无返回
    /// </summary>
    /// <param name="storedProcName"></param>
    /// <param name="Param"></param>
    public void GetProcedureNoRun(string storedProcName, ProceducreParameter[] Param) {

        try
        {
            this._Connection.Open();
            SqlCommand Command = this.BuildQueryCommand(storedProcName, Param);

            Command.ExecuteNonQuery();
        }
        finally
        {
            this._Connection.Close();
            close();

        }
    }

    /// <summary>
    /// 执行SQL语句,返回受影响的行数
    /// </summary>
    /// <param name="Sql">SQL语句</param>
    /// <returns></returns>
    public int ExecuteReRow_SQL(string Sql) {

        int IsOk = 0;
        try
        {
            this._Connection.Open();
            SqlCommand Command = new SqlCommand(Sql, this._Connection);
            Command.CommandType = CommandType.Text;
            IsOk = Command.ExecuteNonQuery();
        }
        finally
        {
            this._Connection.Close();
        }
        return IsOk;
    }

    /// <summary>
    /// 执行存储过程,接收返回值
    /// </summary>
    /// <param name="storedProcName">存储过程名称</param>
    /// <param name="parameters">参数组</param>
    /// <param name="rowsAffected">输出受影响的行数</param>
    /// <returns></returns>
    public int RunProcedure_OutPara(string storedProcName, ProceducreParameter[] parameters, out int rowsAffected)
    {

        int num = 0;
        try
        {
            this._Connection.Open();
            SqlCommand command = this.BuildIntCommand(storedProcName, parameters);
            rowsAffected = command.ExecuteNonQuery();
            if (command.Parameters["ReturnValue"].Value != DBNull.Value)
            {
                num = Convert.ToInt32(command.Parameters["ReturnValue"].Value);
            }
            command.Parameters.Clear();
        }
        finally
        {
            this._Connection.Close();

        }
        return num;
    }






 


    #endregion



    #region 释放Connecction对象

    /// <summary>
    /// 关闭数据库链接
    /// </summary>
    public void close()
    {
        this._Connection.Close();
        this._Connection.Dispose();
    }
    public void Dispose()
    {
        this.dispose(true);
        GC.SuppressFinalize(this);
    }
    /// <summary>
    /// 释放所有资源
    /// </summary>
    /// <param name="disposeing"></param>
    protected virtual void dispose(bool disposeing)
    {
        if (disposeing && (this._Connection != null))
        {
            this._Connection.Dispose();
        }
    }

    /// <summary>
    /// 析构函数
    /// </summary>
    ~dbclass()
    {
        this.dispose(false);
    }
    #endregion

}
